package com.mach.platform.service;

import cn.hutool.core.util.StrUtil;
import cn.hutool.db.Entity;
import cn.hutool.db.handler.EntityListHandler;
import cn.hutool.db.sql.SqlExecutor;
import com.google.common.collect.Lists;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.EnableScheduling;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.Duration;
import java.time.Instant;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.time.temporal.Temporal;
import java.util.*;
import java.util.stream.Collectors;

import static ch.qos.logback.core.db.DBHelper.closeConnection;
import static java.lang.Math.random;

/**
 * 定时任务归档数据（比较耗时）
 */

@Component
@EnableScheduling
public class ArchiveDataService {

    private Logger logger = LoggerFactory.getLogger(this.getClass());

    private static Connection connection = null;
    private static final SimpleDateFormat sd = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    private static final DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
    private static final DateTimeFormatter dtfhour = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:00:00");
    private static final DateTimeFormatter dtfminute = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:00");
    private static final DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    private static final String SPLIT_GROUP = "_";
    private static final double MULTIPLE = 5;
    private static final int START_TIME_GRANULARITY = 48;//现在时间往前推小时数
    private static final int END_TIME_GRANULARITY = 24;//现在时间往前推小时数
    private static final double CUT_VALUE = 0.1;//随机数削值范围，越大数值之间差值越大
    private static String START_TIME = "2019-10-27 20:00:00";
    private static String END_TIME = "2019-10-28 01:00:00";

    private static String BUS_START_TIME = "2019-10-27 20:00:00";
    private static String BUS_END_TIME = "2019-10-27 20:00:00";

    private static final DecimalFormat decimalFormat =new DecimalFormat("#.00");

    @Autowired
    private DataSource ds;

    private Connection getConn(){
        try {
            return ds.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }


    public static List<Entity> getDbData(Connection conn) {

        String sqlGetDatas = "SELECT win.* \n" +
                "FROM `iot_dev_data_win` win\n" +
                "INNER JOIN energy_struc_prop esp on win.dev_id = esp.dev_id\n" +
                "where \n" +
                "win.data_type = esp.name\n" +
                "and esp.field_type = '用量'\n" +
//                "and win.the_time between (NOW() - interval "+ START_TIME_GRANULARITY +" hour) and (NOW() - interval "+ END_TIME_GRANULARITY +" hour)\n" +
                "and win.the_time between '"+ START_TIME +"' and '"+ END_TIME +"'\n" +
//                "and win.dev_id = 285\n" +
//                "and win.data_type = '_用量'\n" +
                "and esp.energy_struc_id is not null\n" +
//                "and esp.type = 1\n" +
                "GROUP BY win.the_time, win.dev_id, win.data_type\n" +
                "ORDER BY win.the_time\n";
        System.out.println(sqlGetDatas);
        List<Entity> entityList = null;
        try {
            entityList = SqlExecutor.query(conn, sqlGetDatas, new EntityListHandler());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return entityList;
    }

    public static List<Entity> getBusData(Connection conn) throws SQLException {
        String sqlGetBusDatas = "select dev.id, dev.name, is2.`name` as busName, win.dvalue, win.data_type, win.the_time, is1.name as workshopName, is1.id as workshopId\n" +
                "from iot_dev_data_win win\n" +
                "LEFT JOIN iot_space is1 on is1.parent_id = 295\n" +
                "LEFT JOIN iot_space is2 on is1.id = is2.parent_id\n" +
                "LEFT JOIN iot_dev dev on dev.space_id = is2.id\n" +
                "where 1=1\n" +
                "and win.dev_id = dev.id\n" +
//                "and win.the_time between (NOW() - interval 35 minute) and (NOW() - interval 5 minute)\n" +
                "and win.the_time between '"+ BUS_START_TIME +"' and '"+ BUS_END_TIME +"'\n" +
                "GROUP BY win.dev_id, win.the_time, win.data_type\n" +
                "ORDER BY win.id desc;\n";
        System.out.println(sqlGetBusDatas);
        List<Entity> entityList = SqlExecutor.query(conn, sqlGetBusDatas, new EntityListHandler());
        return entityList;
    }

    private static void updateData(List<Entity> entities, Connection conn) {

        String sqlUpdate = "update iot_dev_data_win set dvalue = ? where id = ?";

        for (int index = 0; index < entities.size(); index ++) {
            try {
            if (!Objects.isNull(entities.get(index).get("id"))) {

                SqlExecutor.execute(conn, sqlUpdate, entities.get(index).get("dvalue"), entities.get(index).get("id"));

            }else{
                StringBuffer buffer = new StringBuffer();
                buffer.append("REPLACE into iot_dev_data_win (\n" +
                        "dev_id,\n" +
                        "order_num,\n" +
                        "the_time,\n" +
                        "data_type,\n" +
                        "dvalue\n" +
                        ") VALUES");
                buffer.append(splicSql(entities.get(index)));
                SqlExecutor.execute(conn, buffer.toString());
            }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

    }

    /*public static void insertSql(List<Entity> entities){
        Db db = new Db(ds);
        Dialect dialect = db.getRunner().getDialect();
        Entity entity = entities.get(0);
        entity.setTableName("iot_dev_data_win");
        String sql = SqlBuilder.create(new Wrapper()).insert(entity, dialect.dialectName()).build();
        Object[][] objects = entities.stream().map(LinkedHashMap::values).map(Collection::toArray).toArray(Object[][]::new);
        try {
            int[] ints = SqlExecutor.executeBatch(connection, sql, objects);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }*/

    public static void insertDb(List<Entity> entities, Connection conn){
        StringBuffer buffer = new StringBuffer();
        buffer.append("REPLACE into iot_dev_data_win (\n" +
                "dev_id,\n" +
                "order_num,\n" +
                "the_time,\n" +
                "data_type,\n" +
                "dvalue\n" +
                ") VALUES");
        for (int index = 0; index < entities.size(); index ++){
            buffer.append(splicSql(entities.get(index)));
            if (index != entities.size() - 1){
                buffer.append(",");
            }
        }
        if (entities.size() > 0) {
            try {
                SqlExecutor.execute(conn, buffer.toString());
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    private static StringBuffer splicSql(Entity entitie) {
        StringBuffer buffer = new StringBuffer();
        buffer.append("(");
        buffer.append(entitie.get("dev_id"));
        buffer.append(",");
        buffer.append(entitie.get("order_num"));
        buffer.append(",'");
        buffer.append(entitie.get("the_time"));
        buffer.append("','");
        buffer.append(entitie.get("data_type"));
        buffer.append("',");
        buffer.append(entitie.get("dvalue"));
        buffer.append(")");
        return buffer;
    }

    @Scheduled(cron = "0 20 * * * ?")
    public void initArchiveData(){
        Connection conn = getConn();
        try {
            if (Objects.isNull(conn)){
                logger.info("conn is empty initArchiveData");
                logger.info("try to get conn again");
                conn = getConn();
                if (Objects.isNull(conn)){
                    logger.info("still empty initArchiveData");
                    return;
                }
            }
            START_TIME = getHourTimeByTime(LocalDateTime.now(), -START_TIME_GRANULARITY);
            END_TIME = getHourTimeByTime(LocalDateTime.now(), -END_TIME_GRANULARITY);
            //初始化设备信息
            List<Entity> entities = getDbData(conn);

            //分组
            Map<String, List<Entity>> groupingMap = entities.stream().collect(Collectors.groupingBy((Entity entity) -> fetchGroupKey(entity)));

            List<Entity> upResult = Lists.newArrayList();
            groupingMap.forEach((k, v) -> {
                //数据库时间段不连续处理
                v = handleTimeSlot(v);

                v = dataSortByTime(v);
                //取出全部为零的和后一个数
                List<List<Entity>> dataList = getData(v);

                for (List<Entity> datas : dataList) {

                    List<Entity> entityList = test(datas);
                    upResult.addAll(entityList);
                }
            });

            updateData(upResult, conn);
            logger.info("end initArchiveData");
        } catch (Exception e) {
            e.printStackTrace();
        }  finally {
            closeConnection(conn);
        }
    }


    @Scheduled(cron = "0 0/30 * * * ?")
    public void busArchiveData(){
        Connection conn = getConn();
        try {
            if (Objects.isNull(conn)){
                logger.info("conn is empty busArchiveData");
                logger.info("try to get conn again");
                conn = getConn();
                if (Objects.isNull(conn)){
                    logger.info("still empty busArchiveData");
                    return;
                }
            }
            List<Entity> upResult = Lists.newArrayList();
            BUS_START_TIME = getMinutesTimeByTime(LocalDateTime.now(), -35);
            BUS_END_TIME = getMinutesTimeByTime(LocalDateTime.now(), -5);
            //初始化设备信息
            List<Entity> entities = getBusData(conn);
            //分组
            Map<String, List<Entity>> groupingMap = entities.stream().collect(Collectors.groupingBy((Entity entity) -> fetchGroupKeyToBus(entity)));
            groupingMap.forEach((k, v) -> {
                v = dataSortByTime(v);
                //数据库时间段不连续处理==>分钟级
                List<Entity> entityList = handleTimeSlotByMinute(v);
                upResult.addAll(entityList);
            });
            insertDb(upResult, conn);
            logger.info("end busArchiveData");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeConnection(conn);
        }
    }

    private static List<Entity> handleTimeSlotByMinute(List<Entity> entities) {
        List<Entity> result = Lists.newArrayList();
        if (CollectionUtils.isEmpty(entities)){
            return Collections.emptyList();
        }
        String startTime = BUS_START_TIME;
        String endTime = BUS_END_TIME;
        long num = durationMinutes(startTime, endTime);
        String insertTime = startTime;
        boolean existTime = false;
        for (int index = 0; index < num; index ++) {

            for (Entity entity : entities) {
                String entityTime = formatTime(entity.get("the_time").toString());
                if (Objects.equals(formatTime(insertTime), entityTime)){
                    existTime = true;
                }
                if (compareTime(insertTime, entity.get("the_time").toString()))break;
            }

            if (!existTime){
                Entity frontEntity = getFrontEntityByTime(insertTime, entities);
                if (Objects.isNull(frontEntity)){
                    continue;
                }
                Entity addEntity = new Entity();
                addEntity.put("dev_id", frontEntity.get("id"));
                addEntity.put("order_num", 1);
                addEntity.put("data_type", frontEntity.get("data_type"));
                addEntity.put("the_time", formatTime(insertTime));
                addEntity.put("dvalue", randomDecimal(frontEntity.get("dvalue")));
                result.add(addEntity);
            }
            //获取下一分钟
            insertTime = getNextMinutesTime(insertTime, 1);
            existTime = false;
        }
        return result;
    }



    /**
     * 格式化时间为分钟
     * yyyy-mm-dd HH:mm:00
     * @param time
     * @return
     */
    private static String formatTime(String time) {
        if (Objects.isNull(time)){
            return null;
        }
        LocalDateTime ldt = LocalDateTime.parse(time, dtf);
        return dtfminute.format(ldt);
    }

    /**
     * 获取大于insertTime时间的第一个entity
     * @param insertTime
     * @param entities
     * @return
     */
    private static Entity getFrontEntityByTime(String insertTime, List<Entity> entities) {
        for (Entity entity : entities) {
            if (compareTime(insertTime, entity.get("the_time").toString())){
                return entity;
            }
        }
        return null;
    }

    private static boolean compareTime(String insertTime, String time) {
        return LocalDateTime.parse(insertTime, dtf).isBefore(LocalDateTime.parse(time, dtf));
    }

    /**
     * 时间随机秒数
     * @param insertTime
     * @param ramdomSecond
     * @return
     */
    private static String randomSecondInTime(String insertTime, int ramdomSecond) {
        if (StringUtils.isEmpty(insertTime)){
            return null;
        }

        insertTime = StrUtil.sub(insertTime, 0, 17);
        String second = String.valueOf(ramdomSecond);
        if (ramdomSecond < 10){
            second = "0" + second;
        }
        return insertTime + second;
    }

    /**
     * 随机小数
     * @param dvalue
     * @return
     */
    private static Object randomDecimal(Object dvalue) {
        if (Objects.isNull(dvalue)){
            return null;
        }
        double value = Double.parseDouble(dvalue.toString());
        value = value + Double.parseDouble(decimalFormat.format(random() / 10.0));
        return decimalFormat.format(value);
    }

    /**
     * 生成0到59随机数
     * @return
     */
    private static int ramdomSecond(){
        Random random = new Random();
        return random.nextInt(60);
    }



    private static List<Entity> test(List<Entity> entities) {

//        Map<String, List<Entity>> groupingMap = entities.stream().collect(Collectors.groupingBy((Entity entity) -> fetchGroupKey(entity)));

        List<Entity> entityList = getDoubles(entities);
        return entityList;
    }

    /**
     * 获取dvalue数组
     * @param entities entity数组
     * @return
     */
    private static List<Entity> getDoubles(List<Entity> entities) {
        double maxNum = 0;
        List<Double> doubles = Lists.newArrayList();
        for (Entity entity : entities){
            double value = Double.parseDouble(entity.get("dvalue").toString());
            doubles.add(value);
            if (value > maxNum){
                maxNum = value;
            }
        }
        if (maxNum == 0){
            return Collections.emptyList();
        }
        System.out.println("-------------------------------------------------");
        System.out.println("当前结果==>" + entities.get(0).get("dev_id") + ":" + entities.get(0).get("data_type") + "==>" + doubles);

        //List<Double> judgeResult = judgeAdjustment(doubles);
        List<Double> judgeResult = adjustment(doubles);

        //最后结果
        System.out.println("最后结果==>" + entities.get(0).get("dev_id") + ":" + entities.get(0).get("data_type") + "==>" + doubles);
        System.out.println("-------------------------------------------------");
        System.out.println();
        if (CollectionUtils.isNotEmpty(judgeResult)) {
            for (int index = 0; index < entities.size(); index ++){
                Entity entity = entities.get(index);
                entity.put("dvalue", doubles.get(index));
                entities.set(index, entity);
            }
            return entities;
        }
        return Collections.emptyList();
    }

    /**
     * 时间段不连续处理，补数据0
     * @param entities
     * @return
     */
    private static List<Entity> handleTimeSlot(List<Entity> entities) {
        if (CollectionUtils.isEmpty(entities)){
            return Collections.emptyList();
        }
        String startTime = START_TIME;
        String endTime = END_TIME;
        long num = durationHours(startTime, endTime);
        String insertTime = startTime;

        boolean existTime = false;
        for (int index = 0; index < num + 1; index ++) {

            for (Entity entity : entities) {
                if (Objects.equals(insertTime, entity.get("the_time").toString())){
                    existTime = true;
                }
            }

            if (!existTime){
                Entity addEntity = new Entity();
                addEntity.put("dev_id", entities.get(0).get("dev_id"));
                addEntity.put("order_num", entities.get(0).get("order_num"));
                addEntity.put("data_type", entities.get(0).get("data_type"));
                addEntity.put("the_time", insertTime);
                addEntity.put("dvalue", 0);
                entities.add(addEntity);
            }
            //获取下一小时时间
            insertTime = getNextHourTime(insertTime, 1);
            existTime = false;
        }

        return entities;
    }

    private static List<Entity> dataSortByTime(List<Entity> entities) {
        entities.sort((e1, e2) -> {

            try {
                return df.parse(e1.get("the_time").toString()).compareTo(df.parse(e2.get("the_time").toString()));
            } catch (ParseException e) {
                e.printStackTrace();
            }
            return 1;
        });
        return entities;
    }

    private static List<List<Entity>> getData(List<Entity> entities) {
        List<List<Entity>> result = Lists.newArrayList();//24小时可能存在多段为0的数据
        List<Entity> resultItem = Lists.newArrayList();
        boolean ifAdd = false;
        int notZoneIndex = 0;
        for (int index = 0; index < entities.size(); index ++){
            //小于0的数据不处理
            if (Double.parseDouble(entities.get(index).get("dvalue").toString()) < 0){
                continue;
            }
            if (Double.parseDouble(entities.get(index).get("dvalue").toString()) == 0.0){
                ifAdd = true;
                notZoneIndex = index;
                resultItem.add(entities.get(index));
            }
            if (notZoneIndex != 0 && Double.parseDouble(entities.get(index).get("dvalue").toString()) != 0.0){
                if (ifAdd) {
                    ifAdd = false;
                    resultItem.add(entities.get(index));
                    result.add(resultItem);
                    resultItem = Lists.newArrayList();
                }
            }
        }

        return result;
    }

    private static String fetchGroupKey(Entity entity) {
        return entity.get("dev_id") + SPLIT_GROUP + entity.get("data_type");
    }
    private static String fetchGroupKeyToBus(Entity entity) {
        return entity.get("id").toString() + SPLIT_GROUP + entity.get("data_type");
    }

    /**
     * 判断是否需要调整
     * @param doubles dvalue数组
     * @return
     */
    private static List<Double> judgeAdjustment(List<Double> doubles) {
        List<Double> result = null;
        for (int index = 0; index < doubles.size() - 1; index ++) {
            double number = doubles.get(index);
            double lastNumber = doubles.get(index + 1);
            if (number > lastNumber * MULTIPLE){
                //大于倍数，作出调整
                result = adjustment(doubles);
            }else if (number * MULTIPLE < lastNumber ){
                //大于倍数，作出调整
                result = adjustment(doubles);
            }
        }
        if (Objects.isNull(result)){
            return Collections.emptyList();
        }
        return result;
    }

    /**
     * 调整逻辑
     * @param doubles
     * @return
     */
    private static List<Double> adjustment(List<Double> doubles) {
        List<Double> randomList = Lists.newArrayList();

        OptionalDouble optionalDouble = doubles.stream().mapToDouble(Double::doubleValue).average();
        System.out.println("平均数==>" + optionalDouble.getAsDouble());
        for (int index = 0; index < doubles.size(); index ++) {
            doubles.set(index, optionalDouble.getAsDouble());
        }
        for (int index = 0; index < doubles.size() - 1; index ++) {
            double random = random() * optionalDouble.getAsDouble() * CUT_VALUE;
            random = Double.parseDouble(decimalFormat.format(random));
            //获取随机数
            //random = getRandom(random, optionalDouble.getAsDouble());
            randomList.add(random);
            double averageNumber = averageNumber(doubles.get(index), doubles.get(index + 1));
            doubles.set(index, Double.parseDouble(decimalFormat.format(averageNumber - random)));
            doubles.set(index + 1, Double.parseDouble(decimalFormat.format(averageNumber + random)));
        }
        System.out.println("随机数==>" + randomList);
        return doubles;
    }

    /**
     * 获取随机数策略
     * @param random
     * @return
     */
    private static double getRandom(double random, double as) {
        double result = random - (random() * as - random());
        if (result <= 0 || result >= random){
            result = getRandom(random, as);
        }
        return result;
    }


    private static double averageNumber(Double value, Double lastValue) {
        return (value + lastValue) / 2.0;
    }

    public static Instant handleTime(String time){
        Instant instant = null;
        try {
            instant = sd.parse(time).toInstant();
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return instant;
    }

    /**
     * 日期相隔小时数
     * @param startTime
     * @param endTime
     * @return
     */
    private static long durationHours(String startTime, String endTime) {
        Temporal startInclusive = handleTime(startTime);
        Temporal endExclusive = handleTime(endTime);
        return Duration.between(startInclusive, endExclusive).toHours();
    }

    /**
     * 日期相隔分钟
     *
     * @param startTime
     * @param endTime
     * @return
     */
    public static long durationMinutes(String startTime, String endTime) {
        Temporal startInclusive = handleTime(startTime);
        Temporal endExclusive = handleTime(endTime);
        return Duration.between(startInclusive, endExclusive).toMinutes();
    }

    /**
     * 根据当前时间获取下一小时时间
     * @param time
     * @return
     */
    private static String getNextHourTime(String time, int timeNum) {
        LocalDateTime ldt = LocalDateTime.parse(time,dtf);
        LocalDateTime nextLdt = ldt.plusHours(timeNum);
        return nextLdt.format(dtf);
    }

    /**
     * 根据当前时间获取下一小时时间
     * @return
     */
    private static String getHourTimeByTime(LocalDateTime ldt, int timeNum) {
        LocalDateTime nextLdt = ldt.plusHours(timeNum);
        return nextLdt.format(dtfhour);
    }
    /**
     * 根据当前时间获取下一分钟
     * @return
     */
    private static String getNextMinutesTime(String time, int timeNum) {
        LocalDateTime ldt = LocalDateTime.parse(time,dtf);
        LocalDateTime nextLdt = ldt.plusMinutes(timeNum);
        return nextLdt.format(dtf);
    }
    /**
     * 根据ldt时间的下timeNum个分钟
     * @return
     */
    private static String getMinutesTimeByTime(LocalDateTime ldt, int timeNum) {
        LocalDateTime nextLdt = ldt.plusMinutes(timeNum);
        return nextLdt.format(dtfminute);
    }
}
